package com.zrrd.javaWeb.dao;

import com.zrrd.javaWeb.bean.User;
import com.zrrd.javaWeb.utils.DBUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author WanJl
 * @version 1.0
 * @title UserDao
 * @description 关于User的持久层操作，操作数据库
 * @create 2024/1/19 10:58
 */
public class UserDao {
    /**
     * 查询所有用户
     * @return
     */
    public List<User> queryAll(){
        Connection conn = DBUtils.getConn();
        PreparedStatement pstmt=null;
        ResultSet re=null;
        ArrayList<User> list=null;
        try {
            //3、编写SQL语句
            String sql="SELECT * FROM jms2301_db01.user";
            //4、获取PreparedStatement对象，进行预编译SQL
            pstmt = conn.prepareStatement(sql);
            //5、执行SQL语句，获取查询结果
            re = pstmt.executeQuery();
            //6、处理查询结果
            list = new ArrayList<>();
            while (re.next()){
                int uid = re.getInt(1);
                String username = re.getString(2);
                String password = re.getString(3);
                String realname = re.getString(4);
                int age = re.getInt(5);
                String gender = re.getString(6);
                String phone = re.getString(7);
                list.add(new User(uid,username,password,realname,age,gender,phone));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                re.close();
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return list;
    }

    /**
     * 根据uid查询单个用户
     * @param uid
     * @return
     */
    public User queryOneById(Integer uid){
        //1、获取连接
        Connection conn = DBUtils.getConn();
        //2、编写SQL语句
        String sql="SELECT * FROM jms2301_db01.user WHERE uid = ?";
        User user=null;
        PreparedStatement pstmt=null;
        ResultSet re=null;
        try {
            //3、获取PreparedStatement
            pstmt = conn.prepareStatement(sql);
            //4、传递参数
            pstmt.setInt(1,uid);
            //5、执行SQL语句，并获取执行结果
            re = pstmt.executeQuery();
            //6、处理执行结果
            while (re.next()){
                uid = re.getInt(1);
                String username = re.getString(2);
                System.out.println(username);
                String password = re.getString(3);
                String realname = re.getString(4);
                int age = re.getInt(5);
                String gender = re.getString(6);
                String phone = re.getString(7);
                user=new User(uid,username,password,realname,age,gender,phone);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                re.close();
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }

    /**
     * 根据用户名username查询单个用户
     * @param username
     * @return
     */
    public User queryOneByUsername(String username){
        //1、获取连接
        Connection conn = DBUtils.getConn();
        //2、编写SQL语句
        String sql="SELECT * FROM jms2301_db01.user WHERE username = ?";
        User user=null;
        PreparedStatement pstmt=null;
        ResultSet re=null;
        try {
            //3、获取PreparedStatement
            pstmt = conn.prepareStatement(sql);
            //4、传递参数
            pstmt.setString(1,username);
            //5、执行SQL语句，并获取执行结果
            re = pstmt.executeQuery();
            //6、处理执行结果
            while (re.next()){
                Integer uid = re.getInt(1);
                username = re.getString(2);
                String password = re.getString(3);
                String realname = re.getString(4);
                int age = re.getInt(5);
                String gender = re.getString(6);
                String phone = re.getString(7);
                user=new User(uid,username,password,realname,age,gender,phone);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                re.close();
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return user;
    }

    /**
     * 插入用户的方法
     * @param user
     * @return
     */
    public int insert(User user){
        Connection conn=DBUtils.getConn();
        PreparedStatement pstmt =null;
        String sql="INSERT INTO jms2301_db01.user (username, password, realname, age, gender, phone) VALUES (?,?,?,?,?,?)";
        int count =0;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,user.getUsername());
            pstmt.setString(2,user.getPassword());
            pstmt.setString(3,user.getRealname());
            pstmt.setInt(4,user.getAge());
            pstmt.setString(5,user.getGender());
            pstmt.setString(6,user.getPhone());
            count = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {;
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }

    /**
     * 修改用户的方法
     * @param user
     * @return
     */
    public int update(User user){
        Connection conn=DBUtils.getConn();
        PreparedStatement pstmt =null;
        String sql="UPDATE jms2301_db01.user SET username=?,password=?,realname=?,age=?,gender=?,phone=? WHERE uid=?";
        int count =0;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,user.getUsername());
            pstmt.setString(2,user.getPassword());
            pstmt.setString(3,user.getRealname());
            pstmt.setInt(4,user.getAge());
            pstmt.setString(5,user.getGender());
            pstmt.setString(6,user.getPhone());
            pstmt.setInt(7,user.getUid());
            count=pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {;
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }

    /**
     * 根据id删除用户的方法
     * @param uid
     * @return
     */
    public int delete(Integer uid){
        Connection conn=DBUtils.getConn();
        PreparedStatement pstmt =null;
        String sql="DELETE FROM jms2301_db01.user WHERE uid=?";
        int count =0;
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,uid);
            count=pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }
}
